﻿using HelloLLLLL.Blog.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace HelloLLLLL.Blog.DAL
{
    /// <summary>
    /// 对象拼接sql语句
    /// </summary>
    public class SqlBuilderHelper
    {
        /// <summary>
        /// Insert SQL语句
        /// </summary>
        /// <param name="obj">要转换的对象，不可空</param>
        /// <param name="tableName">要添加的表明，不可空</param>
        /// <returns>
        /// 空
        /// sql语句
        /// </returns>
        public static string InsertSql<T>(T t, string tableName) where T : class
        {
            if (t == null || string.IsNullOrEmpty(tableName))
            {
                return string.Empty;
            }
            string pk = GetPK<T>();
            string columns = GetColmons(t,pk);
            if (string.IsNullOrEmpty(columns))
            {
                return string.Empty;
            }
            string values = GetValues(t,pk);
            if (string.IsNullOrEmpty(values))
            {
                return string.Empty;
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("Insert into " + tableName);
            sql.Append("(" + columns + ")");
            sql.Append(" values(" + values + ")");
            return sql.ToString();
        }

        /// <summary>
        /// BulkInsert SQL语句（批量添加）
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="objs">要转换的对象集合，不可空</param>
        /// <param name="tableName">>要添加的表明，不可空</param>
        /// <returns>
        /// 空
        /// sql语句
        /// </returns>
        public static string BulkInsertSql<T>(List<T> objs, string tableName) where T : class
        {
            if (objs == null || objs.Count == 0 || string.IsNullOrEmpty(tableName))
            {
                return string.Empty;
            }
           

                string pk = GetPK<T>();
                string columns = GetColmons(objs[0], pk);
                if (string.IsNullOrEmpty(columns))
                {
                    return string.Empty;
                }
                string values = string.Join(",", objs.Select(p => string.Format("({0})", GetValues(p,pk))).ToArray());
                StringBuilder sql = new StringBuilder();
                sql.Append("Insert into " + tableName);
                sql.Append("(" + columns + ")");
                sql.Append(" values " + values + "\r\n");
            
            return sql.ToString();
        }

        /// <summary>
        /// 获得类型的列名
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string GetColmons<T>(T obj,string ignore)
        {
            if (obj == null)
            {
                return string.Empty;
            }

            List<string> proName = new List<string>();
            PropertyInfo[] pInfos = obj.GetType().GetProperties();
            for (int i = 0; i <pInfos.Length ; i++)
            {
                if (pInfos[i].Name == ignore)
                {
                    continue;
                }

                var pAs = pInfos[i].CustomAttributes;

                bool hasIgnoreAttr = false;
                foreach (var item in pAs)
                {
                    if (item.AttributeType == typeof(LLNoMapAttribute))
                    {
                        hasIgnoreAttr = true;
                        break;
                    }
                }

                if (hasIgnoreAttr)
                {
                    continue;
                }
                    proName.Add(pInfos[i].Name);
                
            }
           

            return string.Join(",",proName );
        }

        /// <summary>
        /// 获得值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string GetValues<T>(T obj, string ignore)
        {
            if (obj == null)
            {
                return string.Empty;
            }
            List<string> proName = new List<string>();
            PropertyInfo[] pInfos = obj.GetType().GetProperties();
            for (int i = 0; i < pInfos.Length; i++)
            {
                if (pInfos[i].Name == ignore)
                {
                    continue;
                }
                var pAs = pInfos[i].CustomAttributes;

                bool hasIgnoreAttr = false;
                foreach (var item in pAs)
                {
                    if (item.AttributeType == typeof(LLNoMapAttribute))
                    {
                        hasIgnoreAttr = true;
                        break;
                    }
                }

                if (hasIgnoreAttr)
                {
                    continue;
                }
                Type t = pInfos[i].PropertyType;
                if (t != typeof(int) && t != typeof(double) && t != typeof(byte)
                    && t != typeof(short) && t != typeof(float))
                {
                    proName.Add(string.Format("'{0}'", pInfos[i].GetValue(obj)));
                }
                else
                {
                    proName.Add(string.Format("{0}", pInfos[i].GetValue(obj)));
                }
            }


            return string.Join(",", proName);
            //return string.Join(",", obj.GetType().GetProperties().Select(p => string.Format("'{0}'", p.GetValue(obj))).ToArray());
        }

        private static string GetPK<T>() where T : class
        {
            string pkName = string.Empty;
            Type objTye = typeof(T);
            PrimaryKeyAttribute pk;
            foreach (Attribute attr in objTye.GetCustomAttributes(true))
            {
                pk = attr as PrimaryKeyAttribute;
                if (pk != null)
                    return pk.Name;
            }
            return pkName;
        }

        /// <summary>
        /// sql修改语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string UpdateSql<T>(T entity, string tableName) where T : class
        {
            if (entity == null || string.IsNullOrEmpty(tableName))
            {
                return string.Empty;
            }

            string pkName = GetPK<T>();

            if (string.IsNullOrEmpty(pkName))
            {
                return string.Empty;
            }

            object pkValue =null;

            StringBuilder sb = new StringBuilder();
            sb.Append("update ");
            sb.Append(tableName);
            sb.Append(" set ");
            Type type = entity.GetType();
            PropertyInfo[] props = type.GetProperties();
            List<string> paraList = new List<string>();
            foreach (var prop in props)
            {
                if (prop.Name == (string)pkName)
                {
                    pkValue = prop.GetValue(entity);
                }
                else
                {
                    object val = prop.GetValue(entity);
                    if ( val== null)
                    {
                        continue;
                    }
                    if (val != null && (val.ToString() == "0" || val.ToString() == "")){
                        continue;
                    }
                    


                    var pAs = prop.CustomAttributes;
                    bool hasIgnoreAttr = false;
                    foreach (var item in pAs)
                    {
                        if (item.AttributeType == typeof(LLNoMapAttribute))
                        {
                            hasIgnoreAttr = true;
                            break;
                        }
                    }

                    if (hasIgnoreAttr)
                    {
                        continue;
                    }

                    paraList.Add(GetUpdatePara(prop, entity));
                }
            }

            if (paraList.Count == 0)
            {
                return string.Empty;
            }

            sb.Append(string.Join(",", paraList));

            if (pkValue==null)
            {
                throw new Exception("主键不能为空");
            }

            sb.Append(" where ");
            sb.Append(pkName);
            sb.Append(" = ");
            sb.AppendFormat("{0}", pkValue);

            return sb.ToString();
        }

        public static string DeleteSql (string tableName,string field,int val)
        {
            string str = "delete from {0} where {1}={2}";

            return string.Format(str, tableName, field, val);


        }

        public static string DeleteListSql(string tableName, string field, int val)
        {
            string str = "delete from {0} where {1} in ({2})";

            return string.Format(str, tableName, field, val);


        }


        /// <summary>
        /// 获得修改参数
        /// </summary>
        /// <param name="property"></param>
        /// <returns></returns>
        private static string GetUpdatePara<T>(PropertyInfo property, T entity)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" {0}='{1}' ", property.Name, property.GetValue(entity));
            return sb.ToString();
        }

    }
}
